{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime,date\n",
    "missing_values = [\"n/a\",\"na\",\"——\"]\n",
    "mydf = pd.read_excel('代码样本数据.xlsx',na_values=missing_values)\n",
    "#for index in mydf.index:\n",
    "    #print(mydf.loc[index])\n",
    "heads = mydf.columns\n",
    "head_arr = []\n",
    "def convert_to_date(date_str):\n",
    "    parts = date_str.split(\".\")\n",
    "    year = int(parts[0])\n",
    "    month = int(parts[1])\n",
    "    day = int(parts[2])\n",
    "    date_obj = date(year,month,day)\n",
    "    return date_obj\n",
    "for head in heads:\n",
    "    #print(type(head))\n",
    "    \n",
    "    if(head.find(\"公司\") != -1):\n",
    "        print(head.split(\"公司\")[0]+\"公司\")\n",
    "        print(head)\n",
    "    if(head.find(\"日期\") != -1):\n",
    "        date_str = head.split(\"：\")[1]\n",
    "        print(convert_to_date(date_str))\n",
    "for index, row in mydf.iterrows():\n",
    "    print(index) # 输出每行的索引值\n",
    "    print(row[index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "mydf = pd.read_excel('代码样本数据.xlsx',na_values=missing_values)\n",
    "mydf.loc[0].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     a    b    c    d    e\n",
      "0  936  999  673   83  478\n",
      "1  899  998   88  774  839\n",
      "2  571  529  608  312  251\n",
      "3  477  704  407  823   81\n",
      "4  503  987  467  653  368\n",
      "5  224   89  877  360  152\n",
      "6  235   40  454  440  726\n",
      "7  755  245  844  436  480\n",
      "8  169  254  586  390  418\n",
      "9  118  910  331  673  377\n",
      "{\"array1\": [1, 2, 3, 4, 5], \"array2\": [\"a\", \"b\", \"c\", \"d\", \"e\"]}\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "# 两个一维数组示例\n",
    "array1 = [1, 2, 3, 4, 5]\n",
    "array2 = ['a', 'b', 'c', 'd', 'e']\n",
    "\n",
    "# 创建一个包含两个一维数组的字典\n",
    "data = {\n",
    "    \"array1\": array1,\n",
    "    \"array2\": array2\n",
    "}\n",
    "arr = np.random.randint(10,1000,size=(10,5))\n",
    "df = pd.DataFrame(arr,index=None,columns=array2)\n",
    "print(df)\n",
    "# 将字典转换为JSON格式\n",
    "json_data = json.dumps(data)\n",
    "\n",
    "print(json_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 两个数组示例\n",
    "array1 = [1, 2, 3, 4, 5]\n",
    "array2 = ['a', 'b', 'c', 'd', 'e']\n",
    "\n",
    "# 创建DataFrame\n",
    "df = pd.DataFrame({\n",
    "    'Column1': array1,\n",
    "    'Column2': array2\n",
    "})\n",
    "\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "\n",
    "# 指定要遍历的目录路径\n",
    "directory = r'E:\\工资\\文件'\n",
    "\n",
    "# 使用glob模块匹配所有的Excel文件\n",
    "excel_files = glob.glob(os.path.join(directory, '*.xlsx')) + glob.glob(os.path.join(directory, '*.xls'))\n",
    "print(excel_files)\n",
    "# 遍历所有Excel文件\n",
    "for excel_file in excel_files:\n",
    "    print(excel_file)\n",
    "    # 在这里可以对每个Excel文件进行操作，例如读取内容或进行其他处理\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "\n",
    "# 指定要遍历的目录路径\n",
    "directory =r'E:\\工资\\文件'\n",
    "\n",
    "# 遍历目录及其子目录下的所有Excel文件\n",
    "excel_files = []\n",
    "for root, dirs, files in os.walk(directory):\n",
    "    for file in files:\n",
    "        if file.endswith('.xlsx') or file.endswith('.xls'):\n",
    "            excel_files.append(os.path.join(root, file))\n",
    "\n",
    "# 打印所有Excel文件的路径\n",
    "for excel_file in excel_files:\n",
    "    print(excel_file)\n",
    "    # 在这里可以对每个Excel文件进行操作，例如读取内容或进行其他处理\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import shutil\n",
    "from openpyxl import load_workbook\n",
    "from openpyxl.utils.exceptions import InvalidFileException\n",
    "import pandas as pd\n",
    "import magic\n",
    "from MyException import MyException\n",
    "import zipfile\n",
    "import numpy as np\n",
    "# 指定原始目录和目标目录\n",
    "source_directory = r'E:\\工资\\文件'\n",
    "target_directory = r'E:\\工资\\加密文件'\n",
    "log_info_file = []\n",
    "log_info_path = []\n",
    "normal_file = []\n",
    "# 确保目标目录存在，如果不存在则创建\n",
    "if not os.path.exists(target_directory):\n",
    "    os.makedirs(target_directory)\n",
    "if not os.path.exists(source_directory):\n",
    "    os.makedirs(source_directory)\n",
    "\n",
    "def is_excel_encrypted(file_path):\n",
    "    try:\n",
    "        wb = load_workbook(file_path,read_only=True)\n",
    "        #wb.close()\n",
    "        return False  # Excel文件未加密\n",
    "    except InvalidFileException:\n",
    "        return True  # Excel文件已加密\n",
    "\n",
    "def is_compressed_file(file_path):\n",
    "    mime = magic.Magic(mime=True)\n",
    "    file_type = mime.from_file(file_path)\n",
    "    return 'application/zip' in file_type or 'application/x-gzip' in file_type\n",
    "\n",
    "# 遍历目录及其子目录下的所有Excel文件\n",
    "excel_files = []\n",
    "for root, dirs, files in os.walk(source_directory):\n",
    "    for file in files:\n",
    "        if file.endswith('.xlsx') or file.endswith('.xls'):\n",
    "            excel_files.append(os.path.join(root, file))\n",
    "# with open('文件.txt',mode='w',encoding='utf8') as f:\n",
    "#     for file in excel_files:\n",
    "#         f.write(file+'\\n')\n",
    "try:\n",
    "    # 打印所有Excel文件的路径\n",
    "    for excel_file in excel_files:\n",
    "        # print(excel_file)\n",
    "        if is_compressed_file(excel_file):\n",
    "            continue\n",
    "        elif '~' in excel_file:\n",
    "            continue\n",
    "        # 检测Excel文件是否加密\n",
    "        elif is_excel_encrypted(excel_file):\n",
    "            print(f'{excel_file} 是加密的Excel文件')\n",
    "            source_file = os.path.join(root, excel_file)\n",
    "            target_file = os.path.join(source_directory, excel_file)\n",
    "            log_info_file.append(os.path.basename(excel_file))\n",
    "            log_info_path.append(os.path.join(source_directory,excel_file))\n",
    "            # 使用shutil模块的move函数将文件剪切到目标目录\n",
    "            #shutil.move(source_file, target_file)\n",
    "        else:\n",
    "            normal_file.append(excel_file)\n",
    "            #print(excel_file)\n",
    "            # log_info_file.append(excel_file)\n",
    "            # log_info_path.append(os.path.join(source_directory,excel_file))\n",
    "            #log_info.append(f'{excel_file} 不是加密的Excel文件')\n",
    "        #     # 在这里可以对每个Excel文件进行操作，例如读取内容或进行其他处理\n",
    "except zipfile.BadZipFile as e:\n",
    "    print(e.args)\n",
    "    print('异常信息')\n",
    "\n",
    "\n",
    "    \n",
    "# print(log_info_file)\n",
    "# print(log_info_path)\n",
    "\n",
    "# file_array = np.array(log_info_file)\n",
    "# path_array = np.array(log_info_path)\n",
    "# list_arr = np.concatenate((file_array,path_array),axis=0)\n",
    "# #list_arr = np.array(log_info_file,log_info_path)\n",
    "# result_arr = list_arr.T\n",
    "# print(list_arr)\n",
    "\n",
    "# file_df = pd.DataFrame(result_arr,columns=['文件名称','路径'])\n",
    "# file_df.to_csv('加密文件汇总表.csv')\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    名称  路径\n",
      "id        \n",
      "1    1   6\n",
      "2    2   7\n",
      "3    3   8\n",
      "4    4   9\n",
      "5    5  10\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "a = np.array([1,2,3,4,5])\n",
    "b = np.array([6,7,8,9,10])\n",
    "c = np.array(list(zip(a.T,b.T)))\n",
    "length = len(c)\n",
    "df = pd.DataFrame(c,columns=['名称','路径'])\n",
    "df['id'] = range(1,length+1)\n",
    "df = df.set_index(['id'])\n",
    "print(df)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import magic\n",
    "\n",
    "def is_compressed_file(file_path):\n",
    "    mime = magic.Magic(mime=True)\n",
    "    file_type = mime.from_file(file_path)\n",
    "    return 'application/zip' in file_type or 'application/x-gzip' in file_type\n",
    "\n",
    "# 指定要检测的文件路径\n",
    "file_path = 'your_file_path'\n",
    "\n",
    "# 判断文件是否为压缩文件\n",
    "if is_compressed_file(file_path):\n",
    "    print(f'{file_path} 是压缩文件')\n",
    "else:\n",
    "    print(f'{file_path} 不是压缩文件')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Excel file format cannot be determined, you must specify an engine manually.",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[18], line 17\u001b[0m\n\u001b[0;32m     15\u001b[0m fpath \u001b[38;5;241m=\u001b[39m os\u001b[38;5;241m.\u001b[39mpath\u001b[38;5;241m.\u001b[39mjoin(root,file)\n\u001b[0;32m     16\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m---> 17\u001b[0m     df \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_excel\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfpath\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     19\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m xlrd\u001b[38;5;241m.\u001b[39mbiffh\u001b[38;5;241m.\u001b[39mXLRDError:\n\u001b[0;32m     20\u001b[0m     \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m该文件: \u001b[39m\u001b[38;5;132;01m%s\u001b[39;00m\u001b[38;5;124m 已被加密\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;241m%\u001b[39m (fpath))\n",
      "File \u001b[1;32mc:\\Users\\18027\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\pandas\\io\\excel\\_base.py:495\u001b[0m, in \u001b[0;36mread_excel\u001b[1;34m(io, sheet_name, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, storage_options, dtype_backend, engine_kwargs)\u001b[0m\n\u001b[0;32m    493\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(io, ExcelFile):\n\u001b[0;32m    494\u001b[0m     should_close \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mTrue\u001b[39;00m\n\u001b[1;32m--> 495\u001b[0m     io \u001b[38;5;241m=\u001b[39m \u001b[43mExcelFile\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    496\u001b[0m \u001b[43m        \u001b[49m\u001b[43mio\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    497\u001b[0m \u001b[43m        \u001b[49m\u001b[43mstorage_options\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mstorage_options\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    498\u001b[0m \u001b[43m        \u001b[49m\u001b[43mengine\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    499\u001b[0m \u001b[43m        \u001b[49m\u001b[43mengine_kwargs\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mengine_kwargs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    500\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    501\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m engine \u001b[38;5;129;01mand\u001b[39;00m engine \u001b[38;5;241m!=\u001b[39m io\u001b[38;5;241m.\u001b[39mengine:\n\u001b[0;32m    502\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\n\u001b[0;32m    503\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mEngine should not be specified when passing \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    504\u001b[0m         \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124man ExcelFile - ExcelFile already has the engine set\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    505\u001b[0m     )\n",
      "File \u001b[1;32mc:\\Users\\18027\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\pandas\\io\\excel\\_base.py:1554\u001b[0m, in \u001b[0;36mExcelFile.__init__\u001b[1;34m(self, path_or_buffer, engine, storage_options, engine_kwargs)\u001b[0m\n\u001b[0;32m   1550\u001b[0m     ext \u001b[38;5;241m=\u001b[39m inspect_excel_format(\n\u001b[0;32m   1551\u001b[0m         content_or_path\u001b[38;5;241m=\u001b[39mpath_or_buffer, storage_options\u001b[38;5;241m=\u001b[39mstorage_options\n\u001b[0;32m   1552\u001b[0m     )\n\u001b[0;32m   1553\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m ext \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m-> 1554\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\n\u001b[0;32m   1555\u001b[0m             \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mExcel file format cannot be determined, you must specify \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m   1556\u001b[0m             \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124man engine manually.\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m   1557\u001b[0m         )\n\u001b[0;32m   1559\u001b[0m engine \u001b[38;5;241m=\u001b[39m config\u001b[38;5;241m.\u001b[39mget_option(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mio.excel.\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mext\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m.reader\u001b[39m\u001b[38;5;124m\"\u001b[39m, silent\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m)\n\u001b[0;32m   1560\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m engine \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mauto\u001b[39m\u001b[38;5;124m\"\u001b[39m:\n",
      "\u001b[1;31mValueError\u001b[0m: Excel file format cannot be determined, you must specify an engine manually."
     ]
    }
   ],
   "source": [
    "# 环境： win10/mac皆可，python 3.7\n",
    "\n",
    "import pandas as pd\n",
    "import os\n",
    "import xlrd\n",
    "\n",
    "source_directory = r'E:\\工资\\文件'\n",
    "# fpath = r'C:/.../test.xlsx' win环境\n",
    "\n",
    "#filename = os.path.basename(fpath)  # 获取文件名\n",
    "excel_files = []\n",
    "for root, dirs, files in os.walk(source_directory):\n",
    "    for file in files:\n",
    "        if file.endswith('.xlsx') or file.endswith('.xls'):\n",
    "            fpath = os.path.join(root,file)\n",
    "            try:\n",
    "                df = pd.read_excel(fpath,engine='openpyxl')\n",
    "\n",
    "            except xlrd.biffh.XLRDError:\n",
    "                print(\"该文件: %s 已被加密\" % (fpath))\n",
    "\n",
    "\n",
    "# 若excel被加密，则打印 “该文件: test.xlsx 已被加密”\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import shutil\n",
    "\n",
    "# 指定原始目录和目标目录\n",
    "source_directory = 'source_directory_path'\n",
    "target_directory = 'target_directory_path'\n",
    "\n",
    "# 确保目标目录存在，如果不存在则创建\n",
    "if not os.path.exists(target_directory):\n",
    "    os.makedirs(target_directory)\n",
    "\n",
    "# 遍历原始目录下的所有文件\n",
    "for root, dirs, files in os.walk(source_directory):\n",
    "    for file in files:\n",
    "        source_file = os.path.join(root, file)\n",
    "        target_file = os.path.join(target_directory, file)\n",
    "        # 使用shutil模块的move函数将文件剪切到目标目录\n",
    "        shutil.move(source_file, target_file)\n",
    "        print(f'Moved {source_file} to {target_file}')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
